﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyReport_NetworkJasaTable
    /// </summary>
    public class CompanyReport_NetworkJasaTable : CacheAbstraction
    {
        public DataTable GetNetworkJasaRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetNetworkJasaRekap(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetNetworkJasaRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_NETWORK_JASA_REKAP, 
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

//                String sxQuery =
//                    @"
//                    select '' MONTH, '' YEAR,
//                        SUM(DECODE(STATUS_JAR, 'ONLINE BILLING', 1, 0)) OB_COUNT,
//                        SUM(DECODE(STATUS_JAR, 'ISOLIR', 1, 0)) ISL_COUNT,
//                        SUM(DECODE(STATUS_JAR, 'CABUT', 1, 0)) CBT_COUNT,
//                        SUM(DECODE(STATUS_JAR, 'PROSES ISOLIR', 1, 0)) PI_COUNT,
//                        SUM(DECODE(STATUS_JAR, 'PROSES CABUT', 1, 0)) PC_COUNT
//                    from 
//                        V_REPORT_NETWORK_JASA
//                    where
//                        COMPANY_ID = :prmID
//                        and VALID_FROM_STATUS > :prmStart - 1
//                        and VALID_FROM_STATUS < :prmEnd + 1
//                    ";

                String sxSelect = 
                    "SUM(DECODE(STATUS_JAR, 'ONLINE BILLING', 1, 0)) as \"ONLINE BILLING\", "+
                    "SUM(DECODE(STATUS_JAR, 'ISOLIR', 1, 0)) as \"ISOLIR\", "+
                    "SUM(DECODE(STATUS_JAR, 'CABUT', 1, 0)) as \"CABUT\", " +
                    "SUM(DECODE(STATUS_JAR, 'PROSES ISOLIR', 1, 0)) as \"PROSES ISOLIR\", " +
                    "SUM(DECODE(STATUS_JAR, 'PROSES CABUT', 1, 0)) as \"PROSES CABUT\", " +
                    "VALUE,"+
                    "MONTH,"+
                    "YEAR";
                String sxQuery = String.Format(
                    @"
                    select
                        {0}
                    from 
                        ( 
                            select 
                                TO_CHAR(VALID_FROM_STATUS, 'MON/YY') VALUE,
                                CAST(TO_CHAR(VALID_FROM_STATUS, 'MM') as number) MONTH,
                                CAST(TO_CHAR(VALID_FROM_STATUS, 'YYYY') as number) YEAR,
                                STATUS_JAR
                            from
                                V_REPORT_NETWORK_JASA
                            where
                                COMPANY_ID = :prmID
                                and TRUNC(VALID_FROM_STATUS) >= TRUNC(:prmStart)
                                and TRUNC(VALID_FROM_STATUS) < TRUNC(:prmEnd + 1)
                        )
                    group by
                        VALUE,
                        MONTH,
                        YEAR
                    order by
                        YEAR,
                        MONTH 
                    ",sxSelect);

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetNetworkJasaDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetNetworkJasaDetail(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetNetworkJasaDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_NETWORK_JASA_DETAIL,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmStart;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                lsParams[lsParams.Count - 1].Value = dtmEnd;

                String sxQuery =
                    @"
                    select
                        NETWORK_NO,
                        STATUS_JAR,
                        JASA1,
                        JASA2,
                        JASA3
                    from 
                        V_REPORT_NETWORK_JASA
                    where
                        COMPANY_ID = :prmID
                        and TRUNC(VALID_FROM_STATUS) > TRUNC(:prmStart - 1)
                        and TRUNC(VALID_FROM_STATUS) < TRUNC(:prmEnd + 1)
                    order by 
                        NETWORK_NO desc
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_REPORT_VIEWS;
        }
    }
}